﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using ASoft.Base;

/*
SELECT t.fields FROM ( 
     SELECT TOP x id_field, sort_field FROM ( 
          SELECT TOP y id_field, sort_field       
          FROM table 
          WHERE conditions 
          ORDER BY sort_field ASC, id_field ASC) AS foo 
     ORDER BY sort_field DESC, id_field DESC) AS bar 
INNER JOIN table AS t ON bar.id_field = t.id_field 
ORDER BY bar.sort_field ASC, bar.id_field ASC
 */

namespace ASoft.Base
{
    public abstract class ARepository<T> : IRepository<T> where T : class,new()
    {
        public virtual void Insert(T entity)
        {
            if (Conn.State != ConnectionState.Open)
            {
                Conn.Open();
            }

            OleDbCommand command = new OleDbCommand(GetInsertSql(entity), Conn);
            command.ExecuteNonQuery();

            Conn.Close();
        }

        protected virtual string GetInsertSql(T entity)
        {
            List<string> columnNames = new List<string>();
            List<string> columnValues = new List<string>();
            typeof(T).GetProperties()
                .Where(p => p.GetCustomAttributes(typeof(EntityColumnAttribute), true).Count() > 0)
                .Where(p => !string.Equals(p.Name, this.KeyName))
                .ToList()
                .ForEach(p =>
                {
                    columnNames.Add(p.Name);
                    columnValues.Add(FormatInsertValue(p.GetValue(entity, null)));
                });
            return string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
                TableName,
                string.Join(",", columnNames.ToArray()),
                string.Join(",", columnValues.ToArray()));

        }

        string FormatInsertValue(object value)
        {
            if (value is DateTime)
            {
                return "'" + ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (value is string)
            {
                return "'" + value.ToString().Replace("'", "''") + "'";
            }
            return value.ToString();
        }

        public virtual List<T> GetEntities(Dictionary<string, string> condition)
        {
            return GetEntities(condition, 0, 0, null, null, false);
        }

        public PageResult<T> GetPageResult(Dictionary<string, string> condition, int pageIndex, int pageSize, string[] sortNames, string[] sortTypes)
        {
            var datas = GetEntities(condition, pageIndex, pageSize, sortNames, sortTypes);
            var dataCount = GetCount(condition);
            return new PageResult<T>(datas, pageIndex, pageSize, dataCount);
        }

        public int GetCount(Dictionary<string, string> condition)
        {
            var sql = string.Format("select count(*) from {0} where {1}",
                TableName,
                GetConditionString(condition));
            OleDbCommand command = new OleDbCommand(sql, Conn);
            if (Conn.State != System.Data.ConnectionState.Open)
            {
                Conn.Open();
            }
            var result = (int)command.ExecuteScalar();
            Conn.Close();
            return result;
        }

        public List<T> GetEntities(Dictionary<string, string> condition,
            int pageIndex, int pageSize,
            string[] sortNames, string[] sortTypes)
        {
            return GetEntities(condition, pageIndex, pageSize, sortNames, sortTypes, true);
        }

        public List<T> GetEntities(Dictionary<string, string> condition,
            int pageIndex, int pageSize,
            string[] sortNames, string[] sortTypes,
            bool isPaging)
        {

            var result = new List<T>();

            OleDbCommand command = new OleDbCommand(GetQueryString(condition, pageIndex, pageSize, sortNames, sortTypes, isPaging), Conn);
            if (Conn.State != System.Data.ConnectionState.Open)
            {
                Conn.Open();
            }
            var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            while (reader.Read())
            {
                var t = new T();
                typeof(T).GetProperties()
                .Where(p => p.GetCustomAttributes(typeof(EntityColumnAttribute), true).Count() > 0)
                .ToList()
                .ForEach(p =>
                {
                    var i = reader.GetOrdinal(p.Name);
                    p.SetValue(t, reader.GetValue(i), null);
                });
                result.Add(t);
            }
            Conn.Close();
            return result;
        }

        protected T GetTopOne(string sql)
        {
            OleDbCommand command = new OleDbCommand(sql, Conn);
            if (Conn.State != System.Data.ConnectionState.Open)
            {
                Conn.Open();
            }
            var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            if (reader.Read())
            {
                var t = new T();
                typeof(T).GetProperties()
                .Where(p => p.GetCustomAttributes(typeof(EntityColumnAttribute), true).Count() > 0)
                .ToList()
                .ForEach(p =>
                {
                    var i = reader.GetOrdinal(p.Name);
                    p.SetValue(t, reader.GetValue(i), null);
                });
                Conn.Close();
                return t;
            }
            Conn.Close();
            return default(T);
        }

        string GetQueryString(Dictionary<string, string> condition, int pageIndex, int pageSize, string[] sortNames, string[] sortTypes, bool isPaging)
        {
            var conditionString = GetConditionString(condition);

            string orderBy = "";

            if (sortNames != null && sortNames.Length > 0)
            {

                string[] orders = new string[sortNames.Length];

                for (int i = 0; i < orders.Length; i++)
                {
                    orders[i] = sortNames[i] + " " + sortTypes[i];
                }

                orderBy = string.Join(",", orders);
            }
            else
            {
                orderBy = KeyName;
            }

            if (!isPaging)
            {
                return string.Format("select * from {0} where {1} {2}",
                    TableName,
                    conditionString,
                    string.IsNullOrWhiteSpace(orderBy) ? "" : "order by " + orderBy);
            }

            pageIndex = pageIndex < 1 ? 1 : pageIndex;
            pageSize = pageSize < 1 ? 20 : pageSize;
            var sortName = KeyName;
            var sortType = "asc";
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                var matchs = Regex.Matches(orderBy, @"\w+\b");
                if (matchs.Count == 2)
                {
                    sortName = matchs[0].Value;
                    sortType = matchs[1].Value;
                }
            }
            return GetSring(pageIndex, pageSize, sortName, sortType, conditionString);

        }

        private string GetConditionString(Dictionary<string, string> condition)
        {
            var andList = new List<string>();
            if (condition != null && condition.Count > 0)
            {
                foreach (var item in condition)
                {
                    if (string.IsNullOrWhiteSpace(item.Value))
                    {
                        continue;
                    }
                    andList.Add(string.Format("({0})", GetOrConditionString(item.Key, item.Value)));
                }
            }
            var conditionString = "1=1";
            if (andList.Count > 0)
            {
                conditionString = string.Join(" AND ", andList.ToArray());
            }
            return conditionString;
        }

        string GetSring(int pageIndex, int pageSize, string sortName, string sortType, string condition)
        {
            if (string.IsNullOrWhiteSpace(sortType))
            {
                sortType = "asc";
            }
            var sortType1 = string.Equals(sortType, "asc", StringComparison.CurrentCultureIgnoreCase) ? "desc" : "asc";

            string sql = @"
SELECT t.* FROM ( 
     SELECT TOP {0} IID, {3} FROM (
          SELECT TOP {1} {2} as IID, {3}
          FROM {4}
          WHERE {7}
          ORDER BY {3} {5}) AS foo
     ORDER BY {3} {6}) AS bar
INNER JOIN {4} AS t ON bar.IID = t.{2}
ORDER BY t.{3} {5}
";
            return string.Format(sql,
                pageSize, //
                pageIndex * pageSize,//skip size + size
                KeyName,
                sortName,
                TableName,
                sortType,
                sortType1,
                condition
                );
        }

        string GetOrConditionString(string columnName, string conditionString)
        {
            var orList = new List<string>();
            foreach (Match match in Regex.Matches(conditionString, @"([^,]+)"))
            {
                var str = GetRangeConditionString(columnName, match.Value);
                if (string.IsNullOrWhiteSpace(str))
                {
                    orList.Add(string.Format("{0} = {1}", columnName, FormatQueryValue(columnName, match.Value)));
                }
                else
                {
                    orList.Add(str);
                }
            }
            return string.Join(" OR ", orList.ToArray());

        }
        string GetRangeConditionString(string columnName, string conditionString)
        {
            if (conditionString.Contains(":"))
            {
                var ary = conditionString.Split(':');
                if (string.IsNullOrWhiteSpace(ary[0]))
                {
                    return string.Format("{0} <= {1}", columnName, FormatQueryValue(columnName, ary[1]));
                }
                else if (string.IsNullOrWhiteSpace(ary[1]))
                {
                    return string.Format("{0} >= {1}", columnName, FormatQueryValue(columnName, ary[0]));
                }
                else if (string.IsNullOrWhiteSpace(ary[0]) && string.IsNullOrWhiteSpace(ary[1]))
                {
                    return string.Empty;
                }
                else
                {
                    return string.Format("{0} >= {1} AND {0} <={2}",
                        columnName,
                        FormatQueryValue(columnName, ary[0]),
                        FormatQueryValue(columnName, ary[1]));
                }
            }
            return string.Empty;
        }

        string FormatQueryValue(string columnName, string conditionString)
        {
            return ColumnFormatDict[columnName.ToLower()](conditionString);
        }

        Dictionary<string, Func<string, string>> columnFormatDict = null;
        Dictionary<string, Func<string, string>> ColumnFormatDict
        {
            get
            {
                if (columnFormatDict == null)
                {
                    columnFormatDict = new Dictionary<string, Func<string, string>>();
                    typeof(T).GetProperties()
                        .Where(p => p.GetCustomAttributes(typeof(EntityColumnAttribute), true).Count() > 0)
                        .ToList()
                        .ForEach(p =>
                        {
                            if (p.PropertyType.Equals(typeof(string)))
                            {
                                columnFormatDict[p.Name.ToLower()] = (x) => "'" + x.Replace("'", "''") + "'";
                            }
                            else if (p.PropertyType.Equals(typeof(DateTime)))
                            {
                                columnFormatDict[p.Name.ToLower()] = (x) => "#" + x.Replace("'", "''") + "#";
                            }
                            else
                            {
                                columnFormatDict[p.Name.ToLower()] = (x) => x;
                            }
                        });
                }
                return columnFormatDict;
            }
        }

        protected virtual string AccessConnString
        {
            get
            {
                //#if DEBUG
                //                return string.Format("Provider=Microsoft.ace.OLEDB.12.0;Data Source={0}{1}.mdb;User Id=admin;Password=;",
                //                   ConfigInfo.RootPath,
                //                   string.IsNullOrWhiteSpace(DBFileName) ? DateTime.Now.Year.ToString() : DBFileName);

                //#else
                //                return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}{1}.mdb;User Id=admin;Password=;",
                //                 ConfigInfo.RootPath,
                //                  string.IsNullOrWhiteSpace(DBFileName) ? DateTime.Now.Year.ToString() : DBFileName);
                //#endif
                return string.Format(ConfigInfo.AccessConnectionString,
                    string.Format("{0}{1}.mdb",
                        ConfigInfo.RootPath,
                        string.IsNullOrWhiteSpace(DBFileName) ? DateTime.Now.Year.ToString() : DBFileName));
            }
        }

        public virtual string DBFileName { get; set; }

        OleDbConnection _Conn;
        protected OleDbConnection Conn
        {
            get
            {
                if (_Conn == null)
                {
                    _Conn = new OleDbConnection(AccessConnString);
                }
                return _Conn;
            }
        }

        protected virtual string TableName
        {
            get
            {
                return typeof(T).Name.Replace("Entity", "");
            }
        }

        public virtual string KeyName
        {
            get
            {
                return TableName + "Id";
            }
        }

        public void Update(T entity)
        {
            if (Conn.State != ConnectionState.Open)
            {
                Conn.Open();
            }

            OleDbCommand command = new OleDbCommand(GetUpdateSql(entity), Conn);
            command.ExecuteNonQuery();

            Conn.Close();
        }

        protected virtual string GetUpdateSql(T entity)
        {
            List<string> columns = new List<string>();
            typeof(T).GetProperties()
                .Where(p => p.GetCustomAttributes(typeof(EntityColumnAttribute), true).Count() > 0)
                .Where(p => !string.Equals(p.Name, this.KeyName))
                .ToList()
                .ForEach(p =>
                {
                    columns.Add(string.Format("{0}={1}", p.Name, FormatInsertValue(p.GetValue(entity, null))));
                });
            return string.Format("update {0} set {1} where {2}",
                TableName,
                string.Join(",", columns.ToArray()),
                string.Format("{0}={1}", KeyName, ((dynamic)entity).Id));
        }

        public void Delete(int id)
        {
            if (Conn.State != ConnectionState.Open)
            {
                Conn.Open();
            }
            string del = string.Format("delete from {0} where {1}", TableName, string.Format("{0}={1}", KeyName, id));
            OleDbCommand command = new OleDbCommand();
            command.Connection = Conn;
            OnDeleteBefore(command, id);
            command.CommandText = del;
            command.ExecuteNonQuery();

            Conn.Close();
        }

        protected virtual void OnDeleteBefore(OleDbCommand command, int id)
        { }
    }
}
